package cn.example.util.toolkit;

import cn.example.util.ToolFactory;
import org.apache.poi.POIDocument;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hwpf.HWPFDocument;
import org.apache.poi.hwpf.usermodel.Range;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.ResourceUtils;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Apache POI 工具类
 * Created by WuYB on 2017/8/8.
 */
public class PoiUtil {

    private static final Logger log = LoggerFactory.getLogger(PoiUtil.class);

    /**
     * 导出文档详细信息
     */
    private static final String company = "SpringBoot Demo 公司";
    private static final String website = "https://gitee.com/996876244/SpringBootDemo";
    private static final String author = "SYSTEM";
    private static final String application = "系统批量数据导出程序";
    private static final String title = "系统导出OFFICE";

    private static class SingletonHolder {
        private static final PoiUtil INSTANCE = new PoiUtil();
    }

    private PoiUtil() {
    }

    public static PoiUtil getInstance() {
        return SingletonHolder.INSTANCE;
    }


    /**
     * 导出数据到Excel并输出到文件
     *
     * @param document   Excel文件对象
     * @param exportFile 导出路径
     * @return boolean
     */
    public boolean exportExcelFile(HSSFWorkbook document, String exportFile) {
        try {
            if (document != null) {
                //导出到文件
                createFile(document, exportFile);
                return true;
            }
            return false;
        } catch (IOException e) {
            log.error("导出Excel文件失败", e);
            return false;
        }
    }

    /**
     * 导出数据到Excel并下载
     *
     * @param document    Excel文件对象
     * @param defaultName 默认文件名
     * @param request     HttpServletRequest
     * @param response    HttpServletResponse
     */
    public void exportExcelDownload(HSSFWorkbook document, String defaultName, HttpServletRequest request, HttpServletResponse response) {
        try {
            if (document != null) {
                createDownload(document, defaultName, request, response);
            }
        } catch (IOException e) {
            log.error("导出Word文件失败", e);
        }
    }


    /**
     * 按照模板生成Word并输出到文件
     *
     * @param tmpFile    Word模板路径
     * @param contentMap 参数
     * @param exportFile 导出路径
     * @return boolean
     */
    public boolean exportWordFile(String tmpFile, Map<String, Object> contentMap, String exportFile) {
        try {
            HWPFDocument document = exportWord(tmpFile, contentMap);
            if (document != null) {
                //导出到文件
                createFile(document, exportFile);
                return true;
            }
            return false;
        } catch (IOException e) {
            log.error("导出Word文件失败", e);
            return false;
        }
    }


    /**
     * 按照模板生成Word并下载
     *
     * @param tmpFile     Word模板路径
     * @param contentMap  参数
     * @param defaultName 默认文件名
     * @param request     HttpServletRequest
     * @param response    HttpServletResponse
     */
    public void exportWordDownload(String tmpFile, Map<String, Object> contentMap, String defaultName, HttpServletRequest request, HttpServletResponse response) {
        try {
            HWPFDocument document = exportWord(tmpFile, contentMap);
            if (document != null) {
                createDownload(document, defaultName, request, response);
            }
        } catch (IOException e) {
            log.error("导出Word文件失败", e);
        }
    }

    /**
     * 读取Excel文件
     *
     * @param file Excel文件
     * @return HSSFWorkbook
     */

    public HSSFWorkbook readExcel(File file) {
        try {
            return new HSSFWorkbook(new FileInputStream(file));
        } catch (IOException e) {
            log.error("读取Excel文件失败", e);
            return null;
        }
    }

    /**
     * 读取Excel文件
     *
     * @param file Excel文件
     * @return HSSFSheet
     */
    public HSSFSheet readSheet(File file, int index) {
        HSSFWorkbook book = readExcel(file);
        return book != null ? readSheet(book, index) : null;
    }

    /**
     * 读取Excel文件
     *
     * @param book  Excel文件
     * @param index Sheet索引
     * @return HSSFSheet
     */
    public HSSFSheet readSheet(HSSFWorkbook book, int index) {
        return index < book.getNumberOfSheets() ? book.getSheetAt(Math.abs(index)) : null;
    }

    /**
     * 读取Excel文件某个Sheet中所有行
     *
     * @param file   Excel文件
     * @param index  Sheet索引
     * @param offset 开始行数
     * @return List<HSSFRow>
     */
    public List<HSSFRow> readSheetRows(File file, int index, int offset) {
        HSSFSheet sheet = readSheet(file, index);
        return sheet != null ? readSheetRows(sheet, offset) : null;
    }

    /**
     * 读取Excel文件某个Sheet中所有行
     *
     * @param book   Excel文件
     * @param index  Sheet索引
     * @param offset 开始行数
     * @return List<HSSFRow>
     */
    public List<HSSFRow> readSheetRows(HSSFWorkbook book, int index, int offset) {
        HSSFSheet sheet = readSheet(book, index);
        return sheet != null ? readSheetRows(sheet, offset) : null;
    }

    /**
     * 读取Excel文件某个Sheet中所有行
     *
     * @param sheet  Excel文件Sheet
     * @param offset 开始行数
     * @return List<HSSFRow>
     */
    public List<HSSFRow> readSheetRows(HSSFSheet sheet, int offset) {
        List<HSSFRow> list = new ArrayList<>();
        for (int i = Math.abs(offset); i < sheet.getLastRowNum() + 1; i++) {
            list.add(sheet.getRow(i));
        }
        return list;
    }

    /**
     * 生成本地文件
     *
     * @param document   office文档
     * @param exportFile 生成路径
     * @throws IOException IO异常
     */
    public void createFile(POIDocument document, String exportFile) throws IOException {
        //导出到文件
        FileOutputStream out = new FileOutputStream(exportFile);
        document.write(out);
        out.close();
    }

    /**
     * 生成下载文件
     *
     * @param document    office文档
     * @param defaultName 默认文件名
     * @param request     HttpServletRequest
     * @param response    HttpServletResponse
     * @throws IOException IO异常
     */
    public void createDownload(POIDocument document, String defaultName, HttpServletRequest request, HttpServletResponse response) throws IOException {
        //导出到文件
        response.setContentType("application/octet-stream;charset=UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=\"" + ToolFactory.getUploadUtil().convertFileName(defaultName, request) + "\"");//默认Excel名称
        response.flushBuffer();
        document.write(response.getOutputStream());
    }

    /**
     * 按照模板生成Word
     *
     * @param tmpFile    Word模板路径
     * @param contentMap 参数
     * @return HWPFDocument
     */
    private HWPFDocument exportWord(String tmpFile, Map<String, Object> contentMap) {
        try {
            FileInputStream tempFileInputStream = new FileInputStream(ResourceUtils.getFile("classpath:office/" + tmpFile));
            HWPFDocument document = new HWPFDocument(tempFileInputStream);
            createInformation(document);
            // 读取文本内容
            Range bodyRange = document.getRange();
            // 替换内容
            for (Map.Entry<String, Object> entry : contentMap.entrySet()) {
                if (entry.getValue() != null) {
                    bodyRange.replaceText("${" + entry.getKey() + "}", entry.getValue().toString());
                } else {
                    bodyRange.replaceText("${" + entry.getKey() + "}", "");
                }
            }
            return document;
        } catch (IOException e) {
            log.error("生成Word文件失败", e);
            return null;
        }
    }

    /**
     * 创建标题行
     *
     * @param index 行号
     * @param cols  列数
     * @param val   标题
     * @param style 样式
     * @param sheet 表单对象
     */
    public void createTitleRow(int index, int cols, String val, HSSFCellStyle style, HSSFSheet sheet) {
        HSSFRow row = sheet.createRow(index);//标题
        for (int i = 0; i < cols; i++) {
            createNewCell(i, val, style, row);
        }
        sheet.addMergedRegion(new CellRangeAddress(index, index, 0, cols - 1));//合并单元格
    }

    /**
     * 创建新单元格
     *
     * @param index 列号
     * @param val   值
     * @param style 样式
     * @param row   行对象
     */
    public void createNewCell(int index, String val, HSSFCellStyle style, HSSFRow row) {
        HSSFCell cell = row.createCell(index);
        cell.setCellValue(ToolFactory.getNullUtil().isNull(val) ? "" : val);
        cell.setCellStyle(style);
    }

    /**
     * 设置每列宽度
     *
     * @param cols  每列宽度
     * @param sheet 表单对象
     */
    public void setColWidth(int[] cols, HSSFSheet sheet) {
        //设置宽度
        if (cols != null && cols.length > 0) {
            for (int i = 0; i < cols.length; i++) {
                sheet.setColumnWidth(i, cols[i] * 2 * 256);
            }
        }
    }


    /**
     * 创建文件信息
     *
     * @param document office文件
     */
    private void createInformation(POIDocument document) {
        //文档信息
        document.createInformationProperties();
        document.getDocumentSummaryInformation().setCompany(company);
        SummaryInformation si = document.getSummaryInformation();
        si.setAuthor(author);  //填加文件作者信息
        si.setLastAuthor(author); //填加文件最后保存者信息
        si.setApplicationName(application); //填加文件创建程序信息
        si.setComments(website); //填加文件备注
        si.setTitle(title); //填加xls文件标题信息
        si.setSubject(title);//填加文件主题信息
        si.setCreateDateTime(new Date());
    }

}

